package com.test08.excelpoi05;


import java.io.File;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;

import jxl.Workbook;
import jxl.write.*;
/**
 * @author wang
 * 1.这里默认顺序就是flag的顺序，
 * 2.如果需要修改顺序，需要先修改flag中内容，然后在修改FieldEntity中get()和set()
 */
public class ExecHander {
	private static String[] flag = {"日期","发货人","电话","地址","收货人","电话","地址","货物名称","件数","单号","受理人","备注"};
	
	public static void main(String[] args) throws Exception {
		exec(new File("D:\\1.xls"),5,2,2);
	}
	/**
	 * @param src
	 * @param beginCol 开始的列
	 * @param beginRow 开始的行
	 * @throws Exception
	 */
	public static void exec(File src,int beginCol,int beginRow,int writeSheetNum) throws Exception{
		// 得到一个可写的副本及sheet
		WritableWorkbook writableWorkbook = Workbook.createWorkbook(src, Workbook.getWorkbook(src));// 创建workbook的副本
		WritableSheet writableSheet = writableWorkbook.getSheet(0);
		
		int rsRows = writableSheet.getRows();
		
		//解析表格中内容，获得集合
		String content = null;
		ArrayList<FileldEntity> fileldEntities = new ArrayList<>();
		for (int i = beginRow; i < rsRows; i++) {
			content = writableSheet.getCell(beginCol, i).getContents().trim();
			// 不是需要的内容，就跳过
			if (!isOk(content)) {
				continue;
			}
			fileldEntities.add(convert(content));
		}
		
		//把list写到某个sheet中
		write(writableWorkbook.createSheet("分析结果", writeSheetNum), fileldEntities);
		
		//释放内存
		writableWorkbook.write();
		writableWorkbook.close();// 关闭workbook，释放内存 ---》一定要释放内存
		
	}
	/**
	 * 判断是否是需要的元素格
	 * @param src
	 * @return
	 */
	public static boolean isOk(String src){
		for (String string : flag) {
			if (!StringUtils.contains(src, string)) {
				return false;
			}
		}
		return true;
	}
	/**
	 * 解析每个单元格，封装
	 * @param src
	 * @return
	 */
	public static FileldEntity convert(String src){
		FileldEntity fileldEntity = new FileldEntity();
		String tmp = "";
		for (int i = 0; i < flag.length; i++) {
			if (i==flag.length-1) {
				tmp = StringUtils.substringAfter(src, flag[i]);
			}else{
				//取得两个关键字之间的值
				tmp = StringUtils.substringBetween(src, flag[i], flag[i+1]);
				//把源字符串给切割掉已经获得的
				src = StringUtils.substringAfter(src, tmp);
			}
			
			//获得的值进行切割处理
			if (StringUtils.startsWith(tmp, ":")) {
				tmp = StringUtils.substringAfter(tmp, ":");
			}
			
			if (StringUtils.startsWith(tmp, "：")) {
				tmp = StringUtils.substringAfter(tmp, "：");
			}
			fileldEntity.set(i, tmp.trim());
		}
		return fileldEntity;
	}
	public static void write(WritableSheet writableSheet,List<FileldEntity> fileldEntities) throws Exception{
		Label label = null;
		//写入表头
		label = new Label(0, 0, "计数");
		writableSheet.addCell(label);
		for (int i = 0; i < flag.length; i++) {
			label = new Label(i+1, 0, flag[i]);
			writableSheet.addCell(label);
		}
		
		//写入结果
		for (int i = 0; i < fileldEntities.size(); i++) {
			//计数	
			label = new Label(0, i+1, i+1+"");
			writableSheet.addCell(label);
			
			for (int j = 0; j < flag.length; j++) {
				label = new Label(j+1, i+1, fileldEntities.get(i).get(j));
				writableSheet.addCell(label);
			}
		}
	}
}
